package db;
import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBSportsExercise.java
 * 
 */
public class DBSportsExercise {
	private  Connection con;
	/** Creates a new instance 
	 * of DBSportsExercise */
	public DBSportsExercise() {
		con = DBConnection.getInstance().getDBcon();
	}
	//Implements the methods from the interface
	// get all SportsExercises
	public ArrayList<SportsExercise> getAllSportsExercise(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}
	//find sportsExercise of type and distance:
	public SportsExercise findSportsExercise(String type, double distance,  boolean retriveAssociation)
	{   String wClause = "  type = '" + type + "' AND distance = " + distance + "";
	return singleWhere(wClause, retriveAssociation);
	}

	//find one SportsExercise having the id
	public SportsExercise searchSportsExerciseId(int id, boolean retriveAssociation)
	{
		String wClause = "id = '" + id + "'";
		System.out.println("SearchSportsExercise " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	//insert a SportsExercise
	public int insertSportsExercise(SportsExercise exercise)
	{     
		int rc = -1;
		String query="INSERT INTO SportsExercise(type, distance)  " +
		"VALUES('"+
		exercise.getType()			+ "','"  +
		exercise.getDistance()  	+ "')";   

		System.out.println("insert : " + query);		  					
		try{ // insert new SportsExercise
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Insert exception in SportsExercise db: "+ex);
		}
		return(rc);
	}

	public int updateSportsExercise(SportsExercise exercise)
	{
		SportsExercise exerciseObj  = exercise;
		int rc=-1;

		String query="UPDATE SportsExercise SET "+

		"type ='" + exerciseObj.getType() 	    +"' "+
		"type ='" + exerciseObj.getDistance() 	+"' "+
		" WHERE id = '" + exerciseObj.getId()   + "'";
		System.out.println("Update query:" + query);
		try{ // update SportsExercise
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Update exception in SportsExercise db: "+ex);
		}
		return(rc);
	}

	public int deleteSportsExercise(String id)
	{
		int rc=-1;

		String query="DELETE FROM SportsExercise WHERE id = '" +
		id + "'";
		System.out.println(query);
		try{ // delete from SportsExercise
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Delete exception in SportsExercise db: "+ex);
		}
		return(rc);
	}

	//private methods
	//michWere is used whenever we want to select more than one SportsExercise


	private ArrayList<SportsExercise> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SportsExercise> list = new ArrayList<SportsExercise>();	

		String query =  buildQuery(wClause);

		try{ // read the SportsExercise from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			//int snr=0;
			while( results.next() ){
				SportsExercise sportsExerciseObj = new SportsExercise();
				sportsExerciseObj = buildExercise(results);	
				list.add(sportsExerciseObj);	
			}//end while
			// if retrieveAssociation is true, the supervisor and department
			// has to be selected for each object in the list
			stmt.close();
		}//slut try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}


	//Singelwhere is used when we only select one SportsExercise 	
	private SportsExercise singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SportsExercise sportObj = new SportsExercise();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the SportsExercise from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				sportObj = buildExercise(results);


			}

		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return sportObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM SportsExercise";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an SportsExercise object
	private SportsExercise buildExercise(ResultSet results){   
		SportsExercise sportsObj = new SportsExercise();
		try{
			sportsObj.setId(results.getInt(1));
			sportsObj.setType(results.getString(2));
			sportsObj.setDistance(results.getDouble(3));



		}
		catch(Exception e)
		{
			System.out.println("error in building the SportsExercise object");
		}
		return sportsObj;
	}


}  


